//
//  ViewController.m
//  Demo1-FMDBBase
//
//  Created by jameswatt on 16/3/8.
//  Copyright © 2016年 xuzhixiang1. All rights reserved.
//

#import "ViewController.h"

//注意导入系统库libsqlite3.tbd
#import "FMDB.h"


@interface ViewController ()
{
    //////////
    FMDatabase *db;
}
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    //1.创建数据库
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
//    NSHomeDirectory();
    NSString *dbPath = [path stringByAppendingPathComponent:@"test.sqlite"];
    // 检查指定的文件存不存在，如果不存就创建，如果存在就直接返回对象
    db = [FMDatabase databaseWithPath:dbPath];
    if (db) {
        NSLog(@"创建数据库成功");
    }else {
        NSLog(@"创建数据库失败");
    }
    
    NSLog(@"%@",NSHomeDirectory());
    if ([db open]) {
        NSLog(@"打开数据库成功");
    }else {
        NSLog(@"打开数据库失败");
    }
    
}


//创建表
- (IBAction)createTable:(id)sender {
    //打开
    //先写sql语句
    NSString *sql = @"CREATE TABLE IF NOT EXISTS T_Student (auto_id INTEGER PRIMARY KEY AUTOINCREMENT , sid TEXT NOT NULL ,name TEXT ,age INTEGER ,headimage BLOB)";
    //执行sql语句
   BOOL suc = [db executeUpdate:sql];
    if (suc) {
        NSLog(@"创建表成功");
    }else {
        NSLog(@"创建表失败");
    }
    //关闭
}
//插入数据
- (IBAction)insertData:(id)sender {
    //创建sql
    NSString *sql = @"INSERT INTO T_Student (name,sid,age) VALUES ('小王',?,?)";
    //sql 注入漏洞，拿到后台权限，管理员
//    sql = [NSString stringWithFormat:@"INSERT INTO T_Student (name,sid,age) VALUES ('小王',%@,%@)",@"",@1];
    
    int age = arc4random() % 100;
    //执行sql
    if ([db executeUpdate:sql,@"xx123",@(age)]) {
        NSLog(@"插入数据成功");
    }else {
        NSLog(@"插入失败");
    }
}
//查询数据
- (IBAction)selectData:(id)sender {
    NSString *sql = @"SELECT * FROM T_Student";
    //条件,查询年龄大于 15岁的所有学生信息
//    NSString *sql = @"SELECT * FROM T_Student WHERE age > 15 ORDER BY age DESC LIMIT 0,5";

    //执行sql******** 查询 语句的时候,必须要用 executeQuery，带有返回的结果集
    FMResultSet * res = [db executeQuery:sql];
    // next 方法，指针会往下移动，如果该位置上有值 next 就返回yes，反之，返回NO
    while ([res next]) {
        // 读取一行的信息
       NSString *name = [res stringForColumn:@"name"];
//        name =  [res stringForColumnIndex:2];
        //两种方法都可以取到结果，知道键名的就用第一种取
        NSString *sid = [res stringForColumn:@"sid"];
        
        //年龄是整形
        NSInteger age = [res intForColumn:@"age"];
        //头像 二进制
        NSData *data = [res dataForColumn:@"headimage"];
    //你存的什么值，取的时候 就怎么取
        NSLog(@" 查询的结果 %@ %@ %ld ",name,sid,age);
        
    }
    
}
//修改
- (IBAction)updateData:(id)sender {
    //一般来说 修改操作都会加一个条件
    
    NSString *sql = @"UPDATE T_Student SET name = 'xiaoming' WHERE name = '小王'";
    if ([db executeUpdate:sql]) {
        NSLog(@"修改成功");
    }else {
        NSLog(@"修改失败");
    }
}
//删除
- (IBAction)deleteData:(id)sender {
    //删除名字为 xiaoming 的所有行
    NSString *sql = @"DELETE FROM T_Student WHERE name = ?";
    if ([db executeUpdate:sql,@"xiaoming"]) {
        NSLog(@"删除成功");
    }
    
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end
